<?php
function printYear($date)
{
	return "<td class=\"EventReportYear\" colspan=\"4\">".$date->format("Y")."</td></tr><tr>";
}

function printMonth($date)
{
	return "<td class=\"EventReportMonth\" colspan=\"4\">".$date->format("F")."</td></tr><tr>";
}

function printDay($date)
{
	return "<td class=\"EventReportDay\" colspan=\"4\">".$date->format("j l")."</td></tr><tr>";
}

function compare1($a,$b)
{
//	print_r ($a)."\n";
	if (new DateTime($a['Start']) == new DateTime($b['Start'])) 
	{
		return 0;
	}
	return (new DateTime($a['Start']) < new DateTime($b['Start'])) ? -1 : 1;
}

function getAddresses($database,$group)
{
	$addresses = array();
//	array_push($addresses,"michael.stoddart@stnicholascathedral.co.uk");
//	return $addresses;

	// bodge to get Choral Scholars and Deps on Music List
	if ($group == "Lay Clerks")
		$group = "Lay Clerks' OR n.GroupName='Choral Scholars' OR n.GroupName='Deputies";
	
	$sql = "SELECT * ";
	$sql .= "FROM MusicAdmin_People AS p ";
	$sql .= "INNER JOIN MusicAdmin_Emails AS e ON p.PersonID = e.PersonID ";
	$sql .= "INNER JOIN MusicAdmin_Groups AS g ON g.PersonID = e.PersonID ";
	$sql .= "INNER JOIN MusicAdmin_GroupNames AS n ON g.GroupNameID = n.GroupID ";
	$sql .= "WHERE n.GroupName='".$group."' OR n.GroupName='Music Staff'"; 

//	echo $sql."<br>";
	$database->ExecuteSQL($sql);

	while($event = $database->getRow())
	{
		array_push($addresses,$event['EmailAddress']);
	}
//	print_r($addresses);
//	echo "<br>";

// add Susan Carver for all calendars
	array_push($addresses,"carver.se@pg.com");

// bodge for NSB getting the boys programme
	if($database->Recopy()->getDatabaseName()!="mstoddart_snap_test")
		array_push($addresses,"jclark@newcastleschool.co.uk");

	return $addresses;
}


function mailGroup($database, $group)
{
	
/*	$users= $GLOBALS['USERS'];
//	$array = $users->getUsers();
	print_r($users);
*/

/*	$groups = new Enumeration($database->recopy(),'EventInvolveeNames');
	$groupsarray = $groups->getArray();*/
//	print_r($array);

//	echo "Group: ".$group."<br>\n";
	
	$dateNow = new DateTime("now");
	$date2weeksago = new DateTime("now");
	$date2weeksago->modify('-2 weeks');
	$previousDate = new DateTime("-10 years");


	$messageHTML = "";
	$messageHTML .= "<h1>".$group." Schedule</h1>\n";
	$messageHTML .= "<p>Correct on ".$dateNow->format("F j, Y")."</p>\n";
	$messageHTML .= "<p>Modified events are shown in <font color=\"Orange\">orange</font>. <br>New events are shown in <font color=\"green\">green</font>.</p>";
	$messageHTML .= "<p>To look at the schedules for all our choirs, go to <a href=\"http://www.stnicholascathedralmusic.co.uk/sncm1/shortcuts/members.php\">http://www.stnicholascathedralmusic.co.uk/sncm1/shortcuts/members.php</a> and enter Username = 'choirmember' and Password = 'nicholas'.</p>\n";
		
	$messageHTML .= "<table>\n";
//	echo "hello<br>\n";

	$sql = "SELECT DISTINCT Ev.*,Inv.EventInvolveeNameID,InvN.LongName FROM Events as Ev ";
	$sql .= "INNER JOIN EventInvolvees AS Inv ON Ev.EventID = Inv.EventID ";
	$sql .= "INNER JOIN EventInvolveeNames AS InvN ON Inv.EventInvolveeNameID=InvN.EventInvolveeNameID "; 
	$sql .= "WHERE Ev.Start > '".$dateNow->format("Y-m-d H:i")."' AND Ev.DeletedBy = '' AND InvN.LongName = 'Choir (".$group.")' "; 
	$sql .= "ORDER BY Start,EventID";
//	echo "\n".$sql."\n";
	$database->ExecuteSQL($sql);


	while($event = $database->getRow())
	{
		$status = "old";
// 		if created within 2 weeks it is either new or modified
		if(new DateTime($event['CreatedWhen']) > $date2weeksago)
		{
			// if OriginalRecordID is 0 then it is created - else it's modified
			$status = ($event['OriginalRecordID'] == 0)?"created":"modified";
		}

		if($status=="created") $style = " style=\"color:green;\" ";
		elseif($status=="modified") $style = " style=\"color:orange;\" ";
		else $style = " style=\"color:black;\" ";
		
		$start = new datetime($event['Start']);
		if($start->format("Y")!=$previousDate->format("Y"))$messageHTML .= printYear($start);
		if($start->format("Ym")!=$previousDate->format("Ym"))$messageHTML .= printMonth($start);
		if($start->format("Ymd")!=$previousDate->format("Ymd"))$messageHTML .= printDay($start);
		$previousDate = $start;
		
		$startString = $start->format("H:i");
		
		$end = new datetime($event['End']);
		$endString = $end->format("H:i");
		
		$LocationsStr = "";
		$database2 = $database->Recopy();
		$sql2 = "SELECT Ev.*, Loc.LocationNameID FROM Events as Ev INNER JOIN Locations AS Loc ON Ev.EventID = Loc.EventID ";
		$sql2 .= "WHERE Ev.EventID = ".$event['EventID']; 
//		echo $sql2."\n\n<br><br>";
		$database2->ExecuteSQL($sql2);
		while($locations = $database2->getRow())
		{
//			print_r ($GLOBALS['LOCATIONS']);
//			print_r ($GLOBALS['LOCATIONS'][$locations['LocationNameID']]['LongName']);
//			echo "\n\n<br>";
			
			$LocationsStr .= $GLOBALS['LOCATIONS'][$locations['LocationNameID']]['LongName'].", ";
		}
		
		$messageHTML .= "<tr><td width=\"20px\"></td><td".$style.">".$startString."&#8209;".$endString."</td><td".$style.">".$event['Title']."</td><td".$style.">".$event['Description']."</td><td".$style.">[".substr($LocationsStr,0,-2)."]</td></tr>\n";
	}
		
	$messageHTML .= "</table>\n";
	
	
	$addresses = getAddresses($database->Recopy(),$group);
//	print_r($addresses);
//	print("<br>\n");
//	echo $group."<br><br>";
	
	foreach($addresses as $address)
	{
		$message = new Message();
		$message->addRecipient($address);
		$message->setSubject($group." Choir Schedule as on ".$dateNow->format("F j, Y"));
		$message->setFrom("Cathedral Choir Admin<no-reply@stnicholascathedralmusic.co.uk>");
		$message->addStyle("p, td {font-family: sans-serif;vertical-align:top;font-size: 70%;color:#003366;}");
		$message->addStyle("h1{font-family: sans-serif;font-size: 100%;color:#003366;}");
		$message->addStyle("h2{font-family: sans-serif;font-size: 80%;color:#003366;}");
		$message->addStyle("td.EventReportYear{font-family: sans-serif;font-size: 120%;font-weight: 400; color:#003366;}");
		$message->addStyle("td.EventReportMonth{font-family: sans-serif;font-size: 110%;font-weight: 400; color:#003366;}");
		$message->addStyle("td.EventReportDay{font-family: sans-serif;font-size: 80%;font-weight: 400; color:#003366;}");
		$message->setMessageBody($messageHTML); 
//		echo $address."<br><br>";
//		echo $messageHTML."<br><br>";


		$message->send();
	}
	
}





//echo dirname(__FILE__)."\n";
//echo "go for it<br><br>n";

/*$topdirectory=dirname(__FILE__)."/../../MSWebGUI";
include $topdirectory.'/FileList.php';

$topdirectory=dirname(__FILE__)."/..";
include $topdirectory.'/FileList.php';

$GLOBALS['FILE_SYSTEM_ROOT'] = dirname(dirname(__FILE__));

echo  $GLOBALS['FILE_SYSTEM_ROOT'];

require_once($GLOBALS['FILE_SYSTEM_ROOT']."/model/classes/database/Database.php");
if(!isset($xml->Database))echo "$xml->Database not set";
$databaseXML = $xml->Database;
$GLOBALS['DATABASE'] = new Database($databaseXML->Server,$databaseXML->Database,$databaseXML->Username,$databaseXML->Password);

require_once($GLOBALS['FILE_SYSTEM_ROOT']."/model/classes/utilities/Locations.php");
$locations = new Locations();
$GLOBALS['LOCATIONS'] = $locations->getLocations();*/


//echo dirname(__FILE__)."/../controllers/init.php\n";

require_once(dirname(__FILE__)."/../controllers/init.php");
require_once($FILE_SYSTEM_ROOT."/../MSWebGUI/Mail/Message.php");

$database=$GLOBALS['DATABASE']->Recopy();

mailGroup($database, 'Boys'); 
mailGroup($database, 'Girls');
mailGroup($database, 'Lay Clerks');
mailGroup($database, 'Lanterns');

?>